package com.starbaba.utils;

@SuppressWarnings("all")
public class UnionAllSplittingTable {
	public static StringBuffer UnionAllSplittingTable(String baseSql,String key,int loopTime,String identifier){
		StringBuffer resultSql = new StringBuffer();
		String tmp = baseSql.replaceAll(key, key + identifier + 0);
		resultSql.append(tmp);
		for(int i = 1; i < loopTime; i ++){
			tmp = baseSql.replaceAll(key,key + identifier + i);
			resultSql.append(" union all ");
			resultSql.append(tmp);
		}
		return resultSql;
	}
	public static StringBuffer UnionAllSplittingTable(String baseSql,String[] keys,int loopTime,String identifier){
		StringBuffer resultSql = new StringBuffer();
		String tmp = baseSql.replaceAll(keys[0], keys[0] + identifier + 0);
		for(int i = 1;i < keys.length; i ++){
			tmp = tmp.replaceAll(keys[i], keys[i] + identifier + 0);
		}
		resultSql.append(tmp);
		for(int i = 1; i < loopTime; i ++){
			tmp = baseSql;
			for(int x = 0; x < keys.length; x ++){
				tmp = tmp.replaceAll(keys[x], keys[x] + identifier + i);
			}
//			tmp = baseSql.replaceAll(key,key + identifier + i);
			resultSql.append(" union all ");
			resultSql.append(tmp);
		}
		return resultSql;
	}
	public static void main(String[] args){
//		String baseSql = "SELECT periodid,couponid AS cuid FROM	treasure_purchase_history WHERE	treasure_purchase_history.couponid= 90149 OR treasure_purchase_history.couponid= 88375 OR treasure_purchase_history.couponid= 90173 OR treasure_purchase_history.couponid= 87650 OR treasure_purchase_history.couponid= 90196 OR treasure_purchase_history.couponid= 85668 OR treasure_purchase_history.couponid= 90225 OR treasure_purchase_history.couponid= 84093 OR treasure_purchase_history.couponid = 88717 OR treasure_purchase_history.couponid = 89878 OR treasure_purchase_history.couponid = 90267 OR treasure_purchase_history.couponid = 90295 OR treasure_purchase_history.couponid = 90273 OR treasure_purchase_history.couponid = 90163 OR treasure_purchase_history.couponid = 90317 OR treasure_purchase_history.couponid = 90325 OR treasure_purchase_history.couponid = 90350 OR treasure_purchase_history.couponid = 90329";
//		System.out.print("result is: " + UnionAllSplittingTable(baseSql, "treasure_purchase_history", 64, "_"));
//		String baseSql = "(select th.purchasetime date, th.orderid orderid, tp.periodcode periodcode,tp.pid productid, product.title productname, th.isvest isvest, th.userid userid, th.username username, u.mobnum phone,th.purchasecount * tp.unitprice costmoney, th.channel channel, th.platform platform, country.`name` countryname,country.currencycode currencycode from treasure_purchase_history th left join treasure_period tp on (th.periodid = tp.id) left join treasure_product_content product on (product.product_id = tp.pid) left join treasure_user u on(u.id = th.userid) left join treasure_country country ON (country.`code` = th.countrycode) where 1=1 and product.language_id = 1  and th.isvest = 0 order by date desc)";
//		String baseSql = "(SELECT tph.purchasetime AS date, tph.userid, tph.username, tph.purchasecount, tph.countrycode AS countryCode, tph.channel, tph.platform, tph.periodid FROM treasure_purchase_history tph WHERE tph.isvest = 0 AND tph.purchasetime >= '2016-08-31 00:00:00' AND tph.purchasetime <= '2016-08-31 23:59:59' AND tph.platform = 'android' AND tph.countrycode = 'th' and tph.userid=728334 and (tph.periodid=28011 or tph.periodid=27843 or tph.periodid=27675) ORDER BY date DESC LIMIT 0, 25)";
//		String baseSql = "(select sum(case when tph.isvest = 0 then tph.purchasecount else 0 end ) realbuymoney,sum(case when tph.isvest = 1 then tph.purchasecount ELSE 0 END ) vestbuymoney,tph.periodid as periodid,tph.couponid as couponid from treasure_purchase_history tph where tph.periodid in (30790,30988,31007,31159,31206,31302,31365,31704,31897,31927) group by periodid)";
		String baseSql = "(select tp.periodcode periodcode,tph.purchasetime purchasetime,u.mobnum phone,tpc.title productname,CASE WHEN tp.winning_userid is not null and tp.winning_userid = tph.userid THEN 1 ELSE 0 END haswin,tph.purchasecount purchasecount,tp.winning_number winnumber from treasure_purchase_history tph left join treasure_period tp on (tph.periodid = tp.id) left join treasure_product_content tpc ON (tp.pid = tpc.product_id) left join treasure_user u ON (tph.userid = u.id)where tph.periodid = tp.id and tph.userid = 613183 group by tph.purchasetime order by tph.purchasetime desc LIMIT 0, 25)";
		String[] keys = {"treasure_purchase_history","tph"};
		System.out.println("result is: " + UnionAllSplittingTable(baseSql, keys, 64, "_"));
		System.out.println("UnionAllSplittingTable.main()");
	}
}
